package com.itheima;

import com.alibaba.druid.sql.visitor.SQLASTOutputVisitorUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;

public class POItest {
    @Test
    public void test1() throws IOException {
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("E:\\ordersetting_template.xlsx")));
        //XSSFWorkbook 解析xlsx     读取第一个标签页
        XSSFSheet sheet = excel.getSheetAt(0);
        for (Row row : sheet) {
            if (row.getRowNum() == 0) {
                for (Cell cell : row) {
                    System.out.println(cell.getStringCellValue());
                }
                continue;
            }
            for (Cell cell : row) {
                System.out.println(cell.getNumericCellValue());
            }

        }
        excel.close();
    }
    @Test
    public void test2() throws IOException {
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("E:\\ordersetting_template.xlsx")));
        //XSSFWorkbook 解析xlsx
        XSSFSheet sheet = excel.getSheetAt(0);
        //获得当前工作表中最后一个行号 行号从0开始
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                System.out.println(cell.getDateCellValue());
            }
        }
        excel.close();
    }
    @Test
    public void test3() throws IOException {
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("E:\\ordersetting_template.xlsx")));
        //XSSFWorkbook 解析xlsx
        XSSFSheet sheet = excel.getSheetAt(0);
        //获得当前工作表中最后一个行号 从0开始
        int lastRowNum = sheet.getLastRowNum();
        XSSFRow row = sheet.getRow(0);
        short lastCellNum = row.getLastCellNum();
        for (int j = 0; j < lastCellNum; j++) {
            XSSFCell cell = row.getCell(j);
            System.out.println(cell.getStringCellValue());
        }
        for (int i = 1; i <= lastRowNum; i++) {
            row = sheet.getRow(i);
            lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                System.out.println(cell.getNumericCellValue());
            }
        }
        excel.close();
    }
    //向excel文件写入数据 通过输出流将创建的文件保存到磁盘
    @Test
    public void test4() throws IOException {
        //创建一个excel文件
        XSSFWorkbook excel = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = excel.createSheet("yutz");
        //在工作表中创建行
        XSSFRow title = sheet.createRow(0);
        //创建单元格
        title.createCell(0).setCellValue("姓名");
        title.createCell(1).setCellValue("地址");
        title.createCell(2).setCellValue("年龄");

        XSSFRow dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue("小铭");
        dataRow.createCell(1).setCellValue("杭州");
        dataRow.createCell(2).setCellValue("25");
        //创建输出流
        FileOutputStream outputStream = new FileOutputStream(new File("E:\\hello.xlsx"));
        excel.write(outputStream);
        outputStream.flush();
        excel.close();
    }
}
